✅ Data loaded successfully:

   Rows: 149116

   Columns: 11

   Date range: 2023-01-01 to 2023-06-30

   Missing values: 0

Overview

Row

Total Transactions

214,470

Total Sales Revenue

$698,812

Average Transaction Value

$3.26

Monthly Growth Rate

6.2%

Row

Store Performance Comparison

Products Analysis

Column

Top 10 Products by Revenue

Product Category Performance

Column

Product Type Analysis

Daily Sales Pattern

Detailed Analytics

Row

Product Pricing Analysis

Product Category Type Avg Price Total Sold Revenue
Sustainably Grown Organic Lg Drinking Chocolate Hot chocolate 4.75 4453 21151.75
Dark chocolate Lg Drinking Chocolate Hot chocolate 4.50 4668 21006.00
Latte Rg Coffee Barista Espresso 4.25 4497 19112.25
Cappuccino Lg Coffee Barista Espresso 4.25 4151 17641.75
Morning Sunrise Chai Lg Tea Brewed Chai tea 4.00 4346 17384.00
Latte Coffee Barista Espresso 3.75 4602 17257.50
Jamaican Coffee River Lg Coffee Premium brewed coffee 3.75 4395 16481.25
Sustainably Grown Organic Rg Drinking Chocolate Hot chocolate 3.75 4329 16233.75
Cappuccino Coffee Barista Espresso 3.75 4266 15997.50
Brazilian Lg Coffee Organic brewed coffee 3.50 4317 15109.50
Ethiopia Lg Coffee Gourmet brewed coffee 3.50 4227 14794.50
Dark chocolate Rg Drinking Chocolate Hot chocolate 3.50 4007 14024.50
Spicy Eye Opener Chai Lg Tea Brewed Chai tea 3.10 4404 13652.40
Ethiopia Rg Coffee Gourmet brewed coffee 3.00 4393 13179.00
Brazilian Rg Coffee Organic brewed coffee 3.00 4385 13155.00
Peppermint Lg Tea Brewed herbal tea 3.00 4350 13050.00
English Breakfast Lg Tea Brewed Black tea 3.00 4309 12927.00
Earl Grey Lg Tea Brewed Black tea 3.00 4245 12735.00
Serenity Green Tea Lg Tea Brewed Green tea 3.00 4220 12660.00
Columbian Medium Roast Lg Coffee Gourmet brewed coffee 3.00 4195 12585.00
Traditional Blend Chai Lg Tea Brewed Chai tea 3.00 4174 12522.00
Espresso shot Coffee Barista Espresso 3.00 4165 12495.00
Jamaican Coffee River Rg Coffee Premium brewed coffee 3.10 4018 12455.80
Lemon Grass Lg Tea Brewed herbal tea 3.00 4089 12267.00
Our Old Time Diner Blend Lg Coffee Drip coffee 3.00 3997 11991.00
Earl Grey Rg Tea Brewed Black tea 2.50 4708 11770.00
Civet Cat Coffee beans Premium Beans 45.00 260 11700.00
Chocolate Croissant Bakery Pastry 3.76 3096 11625.98
Morning Sunrise Chai Rg Tea Brewed Chai tea 2.50 4643 11607.50
Peppermint Rg Tea Brewed herbal tea 2.50 4564 11410.00
Columbian Medium Roast Rg Coffee Gourmet brewed coffee 2.50 4547 11367.50
Traditional Blend Chai Rg Tea Brewed Chai tea 2.50 4512 11280.00
Serenity Green Tea Rg Tea Brewed Green tea 2.50 4477 11192.50
Our Old Time Diner Blend Rg Coffee Drip coffee 2.50 4410 11025.00
Lemon Grass Rg Tea Brewed herbal tea 2.50 4325 10812.50
Spicy Eye Opener Chai Rg Tea Brewed Chai tea 2.55 4171 10636.05
English Breakfast Rg Tea Brewed Black tea 2.50 4200 10500.00
Jamaican Coffee River Sm Coffee Premium brewed coffee 2.45 4018 9844.10
Ethiopia Sm Coffee Gourmet brewed coffee 2.20 4433 9752.60
Brazilian Sm Coffee Organic brewed coffee 2.20 4310 9482.00
Our Old Time Diner Blend Sm Coffee Drip coffee 2.00 4484 8968.00
Scottish Cream Scone Bakery Scone 4.51 1985 8949.45
Ouro Brasileiro shot Coffee Barista Espresso 2.70 3262 8902.20
Columbian Medium Roast Sm Coffee Gourmet brewed coffee 2.00 4178 8356.00
Ginger Scone Bakery Scone 3.18 2540 8011.61
Jumbo Savory Scone Bakery Scone 3.76 2028 7626.62
Almond Croissant Bakery Pastry 3.75 1911 7168.13
Croissant Bakery Pastry 3.51 1954 6861.88
Cranberry Scone Bakery Scone 3.26 2092 6818.44
Chocolate Chip Biscotti Bakery Biscotti 3.51 1924 6748.96

Store Comparison Table

Performance Metrics
Product Info
Store Location Total Transactions Total Revenue Avg Transaction Value Unique Products Top Category
Hell’s Kitchen 71737 236511.2 3.30 80 Coffee
Astoria 70991 232243.9 3.27 79 Coffee
Lower Manhattan 71742 230057.2 3.21 80 Coffee
---
title: "Maven Roasters Coffee Sales Dashboard"
output: 
  flexdashboard::flex_dashboard:
    vertical_layout: scroll
    theme: bootstrap
    source_code: embed
---

```{r setup, include=FALSE}
library(flexdashboard)
library(tidyverse)
library(readxl)
library(viridis)
library(plotly)
library(kableExtra)
library(lubridate)

# Set theme for ggplot
theme_set(theme_minimal())
```

```{r data-loading}
# Import and validate data
df <- read_excel("Coffee_Shop_Sales.xlsx")

# Data validation (since we know there are no missing values)
cat("✅ Data loaded successfully:")
cat("\n   Rows:", nrow(df))
cat("\n   Columns:", ncol(df))
cat("\n   Date range:", as.character(min(df$transaction_date)), "to", as.character(max(df$transaction_date)))
cat("\n   Missing values:", sum(is.na(df)))

# Data preprocessing
df <- df %>%
  mutate(
    transaction_date = as.Date(transaction_date),
    month = month(transaction_date, label = TRUE),
    total_sales = transaction_qty * unit_price,
    # Add day of week and hour if time data is available
    weekday = wday(transaction_date, label = TRUE)
  )
```

Overview {data-orientation=rows}
=====================================

Row {data-height=150}
-------------------------------------

### Total Transactions
```{r}
total_transactions <- sum(df$transaction_qty)
valueBox(
  value = format(total_transactions, big.mark = ","),
  caption = "Total Transactions",
  icon = "fa-shopping-cart",
  color = "primary"
)
```

### Total Sales Revenue
```{r}
total_revenue <- sum(df$total_sales, na.rm = TRUE)
valueBox(
  value = paste0("$", format(round(total_revenue), big.mark = ",")),
  caption = "Total Sales Revenue",
  icon = "fa-dollar-sign",
  color = "success"
)
```

### Average Transaction Value
```{r}
avg_transaction <- round(total_revenue / total_transactions, 2)
valueBox(
  value = paste0("$", avg_transaction),
  caption = "Average Transaction Value",
  icon = "fa-calculator",
  color = "info"
)
```

### Monthly Growth Rate
```{r}
# Calculate month-to-month growth
monthly_sales <- df %>%
  group_by(month = floor_date(transaction_date, "month")) %>%
  summarise(monthly_total = sum(total_sales), .groups = "drop") %>%
  arrange(month) %>%
  mutate(growth_rate = (monthly_total - lag(monthly_total)) / lag(monthly_total) * 100)

latest_growth <- round(tail(monthly_sales$growth_rate[!is.na(monthly_sales$growth_rate)], 1), 1)
growth_color <- ifelse(latest_growth >= 0, "success", "danger")

valueBox(
  value = paste0(latest_growth, "%"),
  caption = "Latest Month Growth",
  icon = ifelse(latest_growth >= 0, "fa-arrow-up", "fa-arrow-down"),
  color = growth_color
)
```

Row {data-height=500}
-------------------------------------

### Sales Trends Over Time
```{r}
# Monthly sales trend
monthly_trend <- df %>%
  group_by(month = floor_date(transaction_date, "month")) %>%
  summarise(
    total_sales = sum(total_sales),
    total_transactions = sum(transaction_qty),
    .groups = "drop"
  )

p1 <- plot_ly(monthly_trend, x = ~month) %>%
  add_trace(y = ~total_sales, name = "Sales ($)", type = "scatter", mode = "lines+markers",
            line = list(color = "#2E86AB"), marker = list(color = "#2E86AB")) %>%
  add_trace(y = ~total_transactions * 10, name = "Transactions (x10)", type = "scatter", mode = "lines+markers",
            line = list(color = "#A23B72"), marker = list(color = "#A23B72"), yaxis = "y2") %>%
  layout(
    title = "Sales Revenue and Transaction Trends",
    xaxis = list(title = "Month"),
    yaxis = list(title = "Sales Revenue ($)", side = "left"),
    yaxis2 = list(title = "Transactions", side = "right", overlaying = "y"),
    hovermode = "x unified"
  )
p1
```

### Store Performance Comparison
```{r}
store_performance <- df %>%
  group_by(store_location) %>%
  summarise(
    total_sales = sum(total_sales),
    total_transactions = sum(transaction_qty),
    avg_transaction_value = round(total_sales / total_transactions, 2),
    .groups = "drop"
  ) %>%
  arrange(desc(total_sales))

p2 <- plot_ly(store_performance, x = ~reorder(store_location, total_sales), y = ~total_sales, 
              type = "bar", marker = list(color = ~total_sales, colorscale = "Viridis")) %>%
  layout(
    title = "Total Sales by Store Location",
    xaxis = list(title = "Store Location"),
    yaxis = list(title = "Total Sales ($)"),
    showlegend = FALSE
  )
p2
```

Products Analysis {data-orientation=columns}
=====================================

Column {data-width=400}
-------------------------------------

### Top 10 Products by Revenue
```{r}
top_products_revenue <- df %>%
  group_by(product_detail) %>%
  summarise(
    total_revenue = sum(total_sales),
    total_qty = sum(transaction_qty),
    avg_price = round(mean(unit_price), 2),
    .groups = "drop"
  ) %>%
  arrange(desc(total_revenue)) %>%
  slice_head(n = 10)

p3 <- plot_ly(top_products_revenue, 
              y = ~reorder(product_detail, total_revenue), 
              x = ~total_revenue,
              type = "bar", 
              orientation = "h",
              marker = list(color = ~total_revenue, colorscale = "Plasma")) %>%
  layout(
    title = "Top 10 Products by Revenue",
    xaxis = list(title = "Total Revenue ($)"),
    yaxis = list(title = "Product"),
    margin = list(l = 150)
  )
p3
```

### Product Category Performance
```{r}
category_performance <- df %>%
  group_by(product_category) %>%
  summarise(
    total_sales = sum(total_sales),
    total_qty = sum(transaction_qty),
    product_count = n_distinct(product_detail),
    .groups = "drop"
  ) %>%
  arrange(desc(total_sales))

colors <- c('#FF6B6B', '#4ECDC4', '#45B7D1', '#96CEB4', '#FFEAA7', 
            '#DDA0DD', '#98D8C8', '#F7DC6F', '#BB8FCE')

p4 <- plot_ly(category_performance, 
              labels = ~product_category, 
              values = ~total_sales,
              type = "pie",
              marker = list(colors = colors),
              textinfo = "label+percent",
              textposition = "outside") %>%
  layout(
    title = "Sales Distribution by Product Category",
    showlegend = FALSE
  )
p4
```

Column {data-width=600}
-------------------------------------

### Product Type Analysis
```{r}
# Enhanced product type analysis
product_type_analysis <- df %>%
  group_by(product_type) %>%
  summarise(
    total_revenue = sum(total_sales),
    total_qty = sum(transaction_qty),
    avg_price = round(mean(unit_price), 2),
    avg_qty_per_transaction = round(mean(transaction_qty), 1),
    .groups = "drop"
  ) %>%
  arrange(desc(total_revenue)) %>%
  slice_head(n = 15)

p5 <- plot_ly(product_type_analysis, x = ~reorder(product_type, -total_revenue), y = ~total_revenue,
              type = "bar", marker = list(color = ~avg_price, colorscale = "Viridis", 
                                        colorbar = list(title = "Avg Price ($)"))) %>%
  layout(
    title = "Product Types: Revenue and Average Price",
    xaxis = list(title = "Product Type", tickangle = -45),
    yaxis = list(title = "Total Revenue ($)"),
    margin = list(b = 100)
  )
p5
```

### Daily Sales Pattern
```{r}
# Daily pattern analysis
daily_pattern <- df %>%
  group_by(weekday) %>%
  summarise(
    avg_daily_sales = round(mean(total_sales), 2),
    total_transactions = sum(transaction_qty),
    .groups = "drop"
  )

p6 <- plot_ly(daily_pattern, x = ~weekday, y = ~avg_daily_sales, type = "bar",
              marker = list(color = ~avg_daily_sales, colorscale = "Blues")) %>%
  layout(
    title = "Average Daily Sales by Day of Week",
    xaxis = list(title = "Day of Week"),
    yaxis = list(title = "Average Sales ($)")
  )
p6
```

Detailed Analytics
=====================================

Row
-------------------------------------

### Product Pricing Analysis
```{r}
# Enhanced pricing table with more insights
pricing_analysis <- df %>%
  group_by(product_detail, product_type, product_category) %>%
  summarise(
    avg_price = round(mean(unit_price), 2),
    min_price = round(min(unit_price), 2),
    max_price = round(max(unit_price), 2),
    total_sold = sum(transaction_qty),
    total_revenue = round(sum(total_sales), 2),
    .groups = "drop"
  ) %>%
  arrange(desc(total_revenue)) %>%
  slice_head(n = 50) %>%
  select(
    `Product` = product_detail,
    `Category` = product_category,
    `Type` = product_type,
    `Avg Price` = avg_price,
    `Total Sold` = total_sold,
    `Revenue` = total_revenue
  )

pricing_analysis %>%
  kbl() %>%
  kable_styling(
    bootstrap_options = c("striped", "hover", "condensed"),
    full_width = FALSE,
    font_size = 12
  ) %>%
  scroll_box(height = "500px")
```

### Store Comparison Table
```{r}
# Detailed store comparison
store_details <- df %>%
  group_by(store_location) %>%
  summarise(
    total_transactions = sum(transaction_qty),
    total_revenue = round(sum(total_sales), 2),
    avg_transaction_value = round(sum(total_sales) / sum(transaction_qty), 2),
    unique_products = n_distinct(product_detail),
    top_category = names(sort(table(product_category), decreasing = TRUE))[1],
    .groups = "drop"
  ) %>%
  arrange(desc(total_revenue)) %>%
  rename(
    `Store Location` = store_location,
    `Total Transactions` = total_transactions,
    `Total Revenue` = total_revenue,
    `Avg Transaction Value` = avg_transaction_value,
    `Unique Products` = unique_products,
    `Top Category` = top_category
  )

store_details %>%
  kbl() %>%
  kable_styling(
    bootstrap_options = c("striped", "hover"),
    full_width = TRUE,
    font_size = 14
  ) %>%
  add_header_above(c(" " = 1, "Performance Metrics" = 3, "Product Info" = 2))
```